package com.example.trabajofinal.db;

import java.util.ArrayList;
import java.util.List;

import com.example.trabajofinal.model.User;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper {

	public static final String TABLE_USERS = "users";
	public static final String TABLE_CURSOS = "cursos";
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_USER = "user";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_PWD = "pwd";
	public static final String COLUMN_CURSO = "curso";
	
	private static final String DATABASE_NAME = "users.db";
	private static final int DATABASE_VERSION = 2;
	private Context context;
	private static DbHelper databaseInstance;

	// Database creation sql statement
	private static final String CREATE_TABLE_USERS = "create table "
			+ TABLE_USERS + "(" + COLUMN_ID	+ " INTEGER primary key autoincrement, "
			+ COLUMN_USER + " TEXT not null, "
			+ COLUMN_NAME + " TEXT not null, "
			+ COLUMN_PWD + " TEXT not null);";

	private static final String CREATE_TABLE_CURSOS = "create table "
			+ TABLE_CURSOS + "(" + COLUMN_ID	+ " INTEGER primary key autoincrement, "
			+ COLUMN_USER + " TEXT not null, "
			+ COLUMN_CURSO + " TEXT not null);";

	public static DbHelper getDatabaseInstance(Context context)
	{
		if(databaseInstance == null)
		{
			databaseInstance = new DbHelper(context.getApplicationContext());
		}
		return databaseInstance;
	}

	private DbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase database)
	{
		database.execSQL(CREATE_TABLE_USERS);
		database.execSQL(CREATE_TABLE_CURSOS);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w("Final",	"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CURSOS);
		db.execSQL(CREATE_TABLE_USERS);
		db.execSQL(CREATE_TABLE_CURSOS);
	}

	public Boolean databaseExists()
	{
		if(SQLiteDatabase.openDatabase(context.getDatabasePath(DATABASE_NAME).getPath(), null, 0) != null)
		{
			this.close();
			return true;
		}

		return false;    			
	}

	public void insertUser(String user, String name, String password)
	{
		try{
			ContentValues values = new ContentValues();
			values.put(COLUMN_USER, user);
			values.put(COLUMN_NAME, name);
			values.put(COLUMN_PWD, password);

			getWritableDatabase().insert(TABLE_USERS, null, values);
			this.close();

			Log.i("Final", "Added user: " + user + "(" + name + ")");
		}
		catch(Exception ex){
			Log.e("Final", "User could NOT be added: " + user + "(" + name + ")");
		}
	}
	

	public User getUser(String user)
	{
		User usuario = null;
		Cursor cursor = null;
		String name = null;
		String pwd = null;

		cursor = getReadableDatabase().query(TABLE_USERS, null, COLUMN_USER + "='" + user + "'" , null, null, null, null);
		boolean existe = cursor.moveToFirst();

		if(existe){
			name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME)); 
			pwd = cursor.getString(cursor.getColumnIndex(COLUMN_PWD)); 
			usuario = new User(user, name, pwd);
		}
		this.close();

		if(usuario==null)
			Log.d("Final", "No se econtro usuario: " + user);
		return usuario;
	}
	
	public List<String> getCursos(String user)
	{
		Cursor cursor = null;
		List<String> cursos = new ArrayList<String>();
		
		cursor = getReadableDatabase().query(TABLE_CURSOS, null,  COLUMN_USER + "=='" + user + "'", null, null, null, null);
		
		while(cursor.moveToNext()){
			String curso = cursor.getString( cursor.getColumnIndex(COLUMN_CURSO));
			cursos.add(curso);
		}
		
		return cursos;
	}
	
	public void addCurso(String user, String curso)
	{
		try{
			ContentValues values = new ContentValues();
			values.put(COLUMN_USER, user);
			values.put(COLUMN_CURSO, curso);

			getWritableDatabase().insert(TABLE_CURSOS, null, values);
			this.close();

			Log.i("Final", "Se agrego curso: " + curso);
		}
		catch(Exception ex){
			Log.e("Final", "No se pudo agregar curso: " + curso);
		}
	}

	public void deleteCurso(String user, String curso)
	{
		String where = COLUMN_USER + "='" + user + "'" + " AND " +
					  COLUMN_CURSO + "='" + curso + "'";
		
		getWritableDatabase().delete(TABLE_CURSOS, where, null);
	}
} 
